package com.windfone.dao.impl.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.windfone.dao.ProductExtendDao;
import com.windfone.model.ProductBase;
import com.windfone.model.ProductCameraFunc;
import com.windfone.model.ProductDigiFunc;
import com.windfone.model.ProductMultimediaFunc;

public class ProductExtendDaoImpl implements ProductExtendDao {
	
	private JdbcTemplate jdbcTemplate;
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
		this.jdbcTemplate = jdbcTemplate;
	}
	
	private String  DELETE_BASE_SQL = " DELETE FROM  productbase WHERE id=?";
	public int deleteProductBase(final int keyValue) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				DELETE_BASE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String  UPDATE_BASE_SQL= " UPDATE productbase SET  addressBook=?,messageFuns=?,emailFuns=?," +
			" inputMethod=?, game=?, officeFuns=? ,recording=?,mainFunction=?," +
			"otherFuns=?,waitTime=?,ring=?  WHERE id=? ";
	public int updateProductBase(final ProductBase bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_BASE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
//						ps.setInt(i++,bean.getLangId());
						ps.setString(i++,bean.getAddressBook());
						ps.setString(i++,bean.getMessageFuns());
						ps.setString(i++,bean.getEmailFuns());
						
						ps.setString(i++,bean.getInputMethod());
						ps.setString(i++,bean.getGame());
						ps.setString(i++,bean.getOfficeFuns());
						ps.setString(i++,bean.getRecording());
						ps.setString(i++,bean.getMainFunction());
						
						ps.setString(i++,bean.getOtherFuns());
						ps.setString(i++,bean.getWaitTime());
						ps.setString(i++,bean.getRing()	);
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	String INSERT_BASE_SQL = " INSERT INTO productbase (productId,addressBook,messageFuns,emailFuns," +
			" inputMethod,game,officeFuns,recording,mainFunction," +
			" otherFuns,waitTime,ring) VALUES" +
			"(?,?,?,?,   ?,?,?,?,?,  ?,?,?  )";
	public int insertProductBase(final ProductBase bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_BASE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getProductId());
//						ps.setInt(i++,bean.getLangId());
						ps.setString(i++,bean.getAddressBook());
						ps.setString(i++,bean.getMessageFuns());
						ps.setString(i++,bean.getEmailFuns());
						
						ps.setString(i++,bean.getInputMethod());
						ps.setString(i++,bean.getGame());
						ps.setString(i++,bean.getOfficeFuns());
						ps.setString(i++,bean.getRecording());
						ps.setString(i++,bean.getMainFunction());
						
						ps.setString(i++,bean.getOtherFuns());
						ps.setString(i++,bean.getWaitTime());
						ps.setString(i++,bean.getRing());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	private String  DELETE_CAMERA_SQL = " DELETE FROM  productcamerafunction WHERE id=?";
	public int deleteProductCameraFunc(final int keyValue) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				DELETE_CAMERA_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String  UPDATE_CAMERA_SQL= " UPDATE productcamerafunction SET    header=?,pixel=?,sensorType=?," +
	" flash=?, special=?, videa=?  ,photoQuality=? ,photoEffects=?, captureMode=?, secondHeader=? WHERE id=? ";
	public int updateProductCameraFunc(final ProductCameraFunc bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_CAMERA_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
//						ps.setInt(i++,bean.getLangId());
						ps.setString(i++,bean.getHeader());
						ps.setString(i++,bean.getPixel());
						ps.setString(i++,bean.getSensorType());
						ps.setString(i++,bean.getFlash());
						ps.setString(i++,bean.getSpecial());
						ps.setString(i++,bean.getVidea());
						ps.setString(i++,bean.getPhotoQuality());
						ps.setString(i++,bean.getPhotoEffects());
						ps.setString(i++,bean.getCaptureMode());
						ps.setString(i++,bean.getSecondHeader());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	
	String INSERT_CAMERA_SQL = " INSERT INTO productcamerafunction (productId,header,pixel,sensorType," +
			"flash,special,videa ) VALUES" +
			"(?,?,?,?,   ?,?,?  )";
	public int insertProductCameraFunc(final ProductCameraFunc bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_CAMERA_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getProductId());
//						ps.setInt(i++,bean.getLangId());
						ps.setString(i++,bean.getHeader());
						ps.setString(i++,bean.getPixel());
						ps.setString(i++,bean.getSensorType());
						ps.setString(i++,bean.getFlash());
						ps.setString(i++,bean.getSpecial());
						ps.setString(i++,bean.getVidea());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	private String DELETE_DIGI_SQL= " DELETE FROM  productdigifunction WHERE id=? ";
	public int deleteProductDigiFunc(final int keyValue) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				DELETE_DIGI_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
 
	String  UPDATE_DIGI_SQL= " UPDATE productdigifunction SET    bluetooth=?,dataService=?,javafunctions=?, wap=?," +
			" www=?, dataline=? ,extendsCard=?,wifiwlan=?, gpsDetail=?," +
			"otherdigit=? WHERE id=? ";
	public int updateProductDigiFunc(final ProductDigiFunc bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_DIGI_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
//						ps.setInt(i++,bean.getLangId());
						ps.setString(i++,bean.getBluetooth());
						ps.setString(i++,bean.getDataService());
						ps.setString(i++,bean.getJavafunctions());
						ps.setString(i++,bean.getWap());
						
						ps.setString(i++,bean.getWww());
						ps.setString(i++,bean.getDataline());
						ps.setString(i++,bean.getExtendsCard());
						ps.setString(i++,bean.getWifiwlan());
						ps.setString(i++,bean.getGpsDetail());
						
						ps.setString(i++,bean.getOtherdigit());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String INSERT_DIGI_SQL = " INSERT INTO productdigifunction (productId,bluetooth,dataService,javafunctions," +
	"wap,www,dataline,extendsCard,wifiwlan ," +
	"gpsDetail,otherdigit) VALUES" +
	"(?,?,?,?,   ?,?,?,?,?,  ?,?  )";
 
	public int insertProductDigiFunc(final ProductDigiFunc bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_DIGI_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getProductId());
//						ps.setInt(i++,bean.getLangId());
						ps.setString(i++,bean.getBluetooth());
						ps.setString(i++,bean.getDataService());
						ps.setString(i++,bean.getJavafunctions());
						
						ps.setString(i++,bean.getWap());
						ps.setString(i++,bean.getWww());
						ps.setString(i++,bean.getDataline());
						ps.setString(i++,bean.getExtendsCard());
						ps.setString(i++,bean.getWifiwlan());

						ps.setString(i++,bean.getGpsDetail());
						ps.setString(i++,bean.getOtherdigit());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	private String  DELETE_MULT_SQL = " DELETE FROM  productmultimediafunction WHERE id=?";
	public int deleteProductMultimediaFunc(final int keyValue) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				DELETE_MULT_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String  UPDATE_MULT_SQL= " UPDATE productmultimediafunction SET   multimedia=?,mp3player=?,videaplayer=?, fmRadio=?," +
	" flashplayer=?,streamingMedia=? WHERE id=? ";
	public int updateProductMultimediaFunc(final ProductMultimediaFunc bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				UPDATE_MULT_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
//						ps.setInt(i++,bean.getLangId());
						ps.setString(i++,bean.getMultimedia());
						ps.setString(i++,bean.getMp3player());
						ps.setString(i++,bean.getVideaplayer());
						ps.setString(i++,bean.getFmRadio());
						
						ps.setString(i++,bean.getFlashplayer());
						ps.setString(i++,bean.getStreamingMedia());
						 
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String INSERT_MULT_SQL = " INSERT INTO productmultimediafunction (productId,multimedia,mp3player,videaplayer," +
	"fmRadio,flashplayer,streamingMedia) VALUES" +
	"(?,?,?,?,   ?,? ,? )";
	public int insertProductMultimediaFunc(final ProductMultimediaFunc bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_MULT_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getProductId());
//						ps.setInt(i++,bean.getLangId());
						ps.setString(i++,bean.getMultimedia());
						ps.setString(i++,bean.getMp3player());
						ps.setString(i++,bean.getVideaplayer());
						
						ps.setString(i++,bean.getFmRadio());
						ps.setString(i++,bean.getFlashplayer());
						ps.setString(i++,bean.getStreamingMedia());
						 
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String LOAD_BASE_SQL = "SELECT * FROM productbase WHERE productId=?";
	public ProductBase loadProductBaseByProductId(final int keyValue) {
		final List<ProductBase> result = new ArrayList<ProductBase>();
		try {
			jdbcTemplate.query(
				LOAD_BASE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							ProductBase bean= new ProductBase();
							bean.setProductId( rs.getInt("productId"));
							bean.setId( rs.getInt("id"));
//							bean.setLangId( rs.getInt("langId"));
							bean.setAddressBook( rs.getString("addressBook") );
							bean.setMessageFuns( rs.getString("messageFuns") );
							bean.setEmailFuns( rs.getString("emailFuns") );
							
							bean.setInputMethod( rs.getString("inputMethod") );
							bean.setGame( rs.getString("game") );
							bean.setOfficeFuns( rs.getString("officeFuns") );
							bean.setRecording( rs.getString("recording") );
							bean.setMainFunction( rs.getString("mainFunction") );
							
							bean.setOtherFuns( rs.getString("otherFuns") );
							bean.setWaitTime( rs.getString("waitTime") );
							bean.setRing( rs.getString("ring") );
 							result.add(bean);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0) 
			return result.get(0);
		else 
			return null;
	}

	String LOAD_CAMERA_SQL = " SELECT * FROM  productcamerafunction WHERE productId=?";
	public ProductCameraFunc loadProductCameraFuncByProductId(final int keyValue) {
		final List<ProductCameraFunc> result = new ArrayList<ProductCameraFunc>();
		try {
			jdbcTemplate.query(
					LOAD_CAMERA_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							ProductCameraFunc bean= new ProductCameraFunc();
							bean.setProductId( rs.getInt("productId"));
							bean.setId( rs.getInt("id"));
//							bean.setLangId( rs.getInt("langId"));
							bean.setHeader( rs.getString("header"));
							bean.setPixel(  rs.getString("pixel") );
							bean.setSensorType( rs.getString("sensorType") );
							bean.setFlash(  rs.getString("flash") );
							bean.setSpecial( rs.getString("special") );
							bean.setVidea( rs.getString("videa") );
							bean.setPhotoQuality( rs.getString("photoQuality") );
							bean.setPhotoEffects( rs.getString("photoEffects") );
							bean.setCaptureMode( rs.getString("captureMode") );
							bean.setSecondHeader( rs.getString("secondHeader") );
 							result.add(bean);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0) 
			return result.get(0);
		else 
			return null;
	}

	String LOAD_DIGI_SQL = " SELECT * FROM productdigifunction WHERE productId=?";
	public ProductDigiFunc loadByProductDigiFuncProductId(final int keyValue) {
		final List<ProductDigiFunc> result = new ArrayList<ProductDigiFunc>();
		try {
			jdbcTemplate.query(
					LOAD_DIGI_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							ProductDigiFunc bean= new ProductDigiFunc();
							bean.setProductId( rs.getInt("productId"));
							bean.setId( rs.getInt("id"));
//							bean.setLangId( rs.getInt("langId"));
							bean.setBluetooth(rs.getString("bluetooth"));
							bean.setDataService(rs.getString("dataService"));
							bean.setJavafunctions(rs.getString("javafunctions"));
							
							bean.setWap(rs.getString("wap"));
							bean.setWww(rs.getString("www"));
							bean.setDataline(rs.getString("dataline"));
							bean.setExtendsCard(rs.getString("extendsCard"));
							bean.setWifiwlan(rs.getString("wifiwlan"));

							bean.setGpsDetail(rs.getString("gpsDetail"));
							bean.setOtherdigit(rs.getString("otherdigit"));
							
 							result.add(bean);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0) 
			return result.get(0);
		else 
			return null;
	}

	String LOAD_MULTIMEDIA_SQL = " SELECT * FROM productmultimediafunction WHERE productId=?";
	public ProductMultimediaFunc loadProductMultimediaFuncByProductId(final int keyValue) {
		final List<ProductMultimediaFunc> result = new ArrayList<ProductMultimediaFunc>();
		try {
			jdbcTemplate.query(
					LOAD_MULTIMEDIA_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							ProductMultimediaFunc bean= new ProductMultimediaFunc();
							bean.setProductId( rs.getInt("productId"));
							bean.setId( rs.getInt("id"));
//							bean.setLangId( rs.getInt("langId"));
							bean.setMultimedia(rs.getString("multimedia"));
							bean.setMp3player(rs.getString("mp3player"));
							bean.setVideaplayer(rs.getString("videaplayer"));
							
							bean.setFmRadio(rs.getString("fmRadio"));
							bean.setFlashplayer(rs.getString("flashplayer"));							 
							bean.setStreamingMedia(rs.getString("streamingMedia"));							 
 							result.add(bean);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0) 
			return result.get(0);
		else 
			return null;
	}

	
	private String  DELETE_BASE_BY_PRODUCTID_SQL = " DELETE FROM  productbase WHERE productId=?";
	public int deleteProductBaseByProductId(final int productId) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				DELETE_BASE_BY_PRODUCTID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, productId);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	private String  DELETE_CAMERA_BY_PRODUCTID_SQL = " DELETE FROM  productcamerafunction WHERE productId=?";
	public int deleteProductCameraFuncByProductId(final int productId) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_CAMERA_BY_PRODUCTID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, productId);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	private String DELETE_DIGI_BY_PRODUCTID_SQL= " DELETE FROM  productdigifunction WHERE productId=? ";
	public int deleteProductDigiFuncByProductId(final int productId) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_DIGI_BY_PRODUCTID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, productId);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	private String  DELETE_MULT_BY_PRODUCTID_SQL = " DELETE FROM  productmultimediafunction WHERE productId=?";
	public int deleteProductMultimediaFuncByProductId(final int productId) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				DELETE_MULT_BY_PRODUCTID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, productId);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

}
